-- @owner: @nanyang12
-- @date: 2025/4/1
-- @testpoint:使用postgresql风格创建自定义函数，函数带cost和rows参数

--连接D库
@conn PrimaryDbDmode;
--创建插件
--create extension shark;
-- 创建 employees 表
CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 employee_name VARCHAR(100),
 department_id INT
);
-- 插入示例数据
INSERT INTO employees (employee_name, department_id) VALUES
('Alice', 1), ('Bob', 1), ('Charlie', 2), ('David', 2);
-- 创建自定义函数(函数带cost和rows参数-- 估计函数执行成本为 100 -- 估计函数返回 10 行数据)
CREATE OR REPLACE FUNCTION get_employees_by_department(dept_id INT)
RETURNS SETOF employees
AS $$
BEGIN
 RETURN QUERY SELECT * FROM employees WHERE department_id = dept_id;
END;

$$ LANGUAGE pltsql COST 100  ROWS 10; 
/
-- 调用函数
EXPLAIN ANALYZE SELECT * FROM get_employees_by_department(1);
--通过系统表查询函数信息，成功
select l.lanname from pg_language l join pg_proc p on l.oid = p.prolang and p.proname in ('get_employees_by_department');
--清理环境
drop function if exists get_employees_by_department;
drop table if exists employees;
